const mysql = require("mysql");
const dayjs = require('dayjs');
const scripts = require("./scripts/mysqlMetadata");
const SqlBuilder = require('./SqlBuilder');
const CreateSqlBuilder = require('./CreateSqlBuilder');

const { getDbConfig } = require("../index");
const mysqlStatus = require('./params/mysql-status');

class MySQLHelper {
  constructor(index) {
    this.type = 'mysql';
    this.dbIndex = index;
    this.scripts = scripts;
  }

  /**
   * 执行无返回结果集的查询(添加、更新、删除)。
   * @param {String} sql sql语句
   * @param {any} values 参数值
   * @returns {Promise} 异步操作对象
   */
  execute (sql, values) {
    return executeNonQuery(sql, values, this.dbIndex);
  }

  /**
   * 添加数据
   * @param {String} tableName 表名称
   * @param {any} datas 数据信息
   * @returns {Promise} 异步操作对象
   */
  adds (tableName, datas) {
    let builder = new CreateSqlBuilder(tableName, Array.isArray(datas) ? datas[0] : datas);

    let sql = builder.build()

    if (Array.isArray(datas)) {
      let all = datas.map(d => query(sql, builder.values(d), this.dbIndex));

      return Promise.all(all);
    } else {
      return query(sql, datas, this.dbIndex);
    }
  }

  /**
   * 查询单条数据。
   * @param {String} sql 查询语句
   * @param {Array} values 参数值
   * @param {Function | Number} callback 动态参数设置回调函数或数据库服务器标识(number型)
   * @returns {Promise} 异步操作对象
   */
  queryForObject (sql, values, callback) {
    let tempSql = sql;
    let builder = new SqlBuilder(values);

    if (typeof callback == 'function' && callback != null) {
      callback(builder);
    }

    tempSql += builder.build();

    return new Promise((resolve, reject) => {
      GetDbPool(this.dbIndex).getConnection((error, connection) => {
        if (error) {
          reject(error);
        } else {
          let rs = {
            at: dayjs().format('YYYY-MM-DD HH:mm:ss'),
            success: false
          };

          executeSQL(connection, tempSql, builder.getValues()).then(
            rows => {
              rs.data = rows[0] || {};
              rs.success = true;

              resolve(rs);
              connection.release();
            },
            reason => {
              rs.message = reason.sqlMessage || reason.message;

              reject(rs);
              connection.release();
            }
          );
        }
      });
    });
  }

  /**
   * 查询多条数据。
   * @param {String} sql 查询语句
   * @param {Array} values 参数值
   * @param {Function | Number | String} callback 动态参数设置回调函数或数据库服务器标识(number型)
   * @returns {Promise} 异步操作对象
   */
  queryForList (sql, values, callback = null) {
    if (sql.indexOf('SQL_CALC_FOUND_ROWS') == -1) {
      sql = sql.replace(/select/i, 'SELECT ');
    }

    let tempSql = sql;
    let builder = new SqlBuilder(values);

    if (typeof callback == 'function' && callback != null) {
      callback(builder);
    }

    tempSql += builder.build();

    return new Promise((resolve, reject) => {
      GetDbPool(this.dbIndex).getConnection((error, connection) => {
        if (error) {
          reject(error);
        } else {
          let rs = {
            at: dayjs().format('YYYY-MM-DD HH:mm:ss'),
            success: false
          };

          let promises = [];
          let datasP = executeSQL(connection, tempSql, builder.getValues());

          promises.push(datasP);

          if (builder.enablePaged) {
            let totalsP = executeSQL(connection, builder.getTotalSql(tempSql));

            promises.push(totalsP);
          }

          let all = Promise.all(promises);

          all.then(
            ([dataRs, totalRS]) => {
              rs.success = true;
              rs.datas = dataRs;
              rs.total = totalRS ? totalRS[0]["totals"] : dataRs.length;

              resolve(rs);
              connection.release();
            },
            reason => {
              rs.message = reason;

              reject(rs);
              connection.release();
            }
          );
        }
      });
    });
  };

  async databases () {
    let rs = await this.queryForList(scripts.DataBases);

    return rs;
  }

  /**
   * 获取数据库的表集合。
   * @param {String} dbName 数据库名称
   * @returns {Promise} 异步对象
   */
  tables (dbName) {
    return this.queryForList(scripts.Tables, { dbName });
  }

  /**
   * 获取数据库的索引集合。
   * @param {String} dbName 数据库名称
   * @returns {Promise} 异步对象
   */
  indexs (dbName) {
    return this.queryForList(scripts.TablesIndexs, { dbName });
  }

  /**
   * 获取表的定义信息。
   * @param {String} dbName 数据库名称
   * @returns {Promise} 异步对象
   */
  tableDDL (dbName, tableName) {
    return this.queryForObject(scripts.TableDDL, { dbName, tableName });
  }

  async tableDML (dbName, tableName) {
    let rs = await this.columns(dbName, tableName);

    let dmls = [];

    let querySql = '';
    let orders = '';
    let insertColumns = '';
    let insertValues = '';
    let updateColumns = '';
    let whereColumns = '';

    rs.datas.forEach(column => {
      let seperator = column != rs.datas[rs.datas.length - 1] ? ',\n  ' : '';

      querySql += `\`${column.column_name}\`${seperator}`;

      if (!column.IsIdentity) {
        insertColumns += `\`${column.column_name}\`${seperator}`;
        insertValues += `<${column.column_name}:${column.column_type}>${seperator}`;
      }

      if (!column.IsIdentity && column.column_key != 'PRI') {
        updateColumns += `\`${column.column_name}\`=<${column.column_name}:${column.column_type}>${seperator}`;
      }

      if (column.column_key) {
        orders += (orders.length > 0 ? ', ' : '');
        orders += `[${column.column_name}]`;

        whereColumns += (whereColumns.length > 0 ? ' AND' : '');
        whereColumns += `\`${column.column_name}\`=<${column.column_name}:${column.column_type}>`;
      }
    });

    dmls.push('--查询语句');
    dmls.push(`SELECT\n  ${querySql} \nFROM \`${tableName}\`\nWHERE ${whereColumns};`);

    dmls.push('--分页查询语句');
    dmls.push(`SELECT\n  ${querySql} \nFROM \`${tableName}\`\nORDER BY ${orders}\nLIMIT 0, 20;`);

    dmls.push('--添加数据');
    dmls.push(`INSERT INTO \`${tableName}\`\n(\n  ${insertColumns}\n)\nVALUES\n(\n  ${insertValues}\n);`);

    dmls.push('--更新数据');
    dmls.push(`UPDATE \`${tableName}\`\nSET ${updateColumns}\nWHERE ${whereColumns};`);

    dmls.push('--删除数据');
    dmls.push(`DELETE FROM \`${tableName}\` WHERE ${whereColumns};`);

    return {
      at: dayjs().format('YYYY-MM-DD HH:mm:ss'),
      success: true,
      datas: dmls
    };
  }

  /**
   * 获取表的列信息。
   * @param {String} dbName 数据库
   * @param {String} tableName 表名称
   * @returns {Promise} 异步对象
   */
  columns (dbName, tableName) {
    return this.queryForList(scripts.TableColumns, { dbName, tableName });
  }

  /**
   * 分页获取表数据。
   * @param {String} dbName 数据库名称
   * @param {String} tableName 表名称
   * @param {Object} so 查询条件
   * @returns {Promise} 异步对象。
   */
  tableDatas (dbName, tableName, so) {
    return new Promise((resolve, reject) => {
      GetDbPool(this.dbIndex).getConnection((error, connection) => {
        if (error) {
          reject(error);
        } else {
          so = so || {};
          so.pageIndex = so.pageIndex || 1;
          so.pageSize = so.pageSize || 20;

          let rs = {
            at: dayjs().format('YYYY-MM-DD HH:mm:ss'),
            success: false,
            total: 0,
            page: 1,
            columns: [],
            datas: []
          };

          let builder = new SqlBuilder(so);
          let sql = scripts.TableDatas.replace(/#dbName/ig, `${dbName}`).replace(/#tableName/ig, tableName) + builder.build();

          let datasP = executeSQL(connection, sql, builder.getValues());
          let totalsP = executeSQL(connection, builder.getTotalSql(sql), builder.getValues());
          let columnsP = executeSQL(connection, scripts.TableColumns, { dbName, tableName });

          let all = Promise.all([datasP, totalsP, columnsP]);

          all.then(
            ([dataRs, totalRS, columnsRs]) => {
              rs.success = true;
              rs.datas = dataRs;
              rs.total = totalRS[0]["totals"];
              rs.columns = columnsRs.map(item => {
                return {
                  field: item.column_name,
                  title: item.column_comment || item.column_name,
                  length: item.character_length
                };
              });

              let limit = so.pageSize || 20;
              rs.page = rs.total % limit == 0 ? rs.total / limit : parseInt(rs.total / limit) + 1;

              resolve(rs);
              connection.release();
            },
            reason => {
              reject(reason);
              connection.release();
            }
          );
        }
      });
    });
  }

  columnChangesDdls (changeds) {
    let ddls = [];

    (changeds || []).forEach(item => {
      let table = item.table;

      item.changeds.forEach(column => {
        let sql = "";
        if (column.flag == "add") {
          sql = `ALTER TABLE \`${table}\` ADD COLUMN \`${column.column_name}\``;
          sql += ` ${column.column_type}`;

          if (column.is_nullable == "YES") {
            sql += " NULL";
          } else {
            sql += " NOT NULL";
          }

          if (column.column_default != null) {
            sql += ` DEFAULT ${GetColumnDefaultValue(column.column_default)}`;
          }

          sql += ` COMMENT '${column.column_comment}';`;
        } else {
          sql = `ALTER TABLE \`${table}\` MODIFY COLUMN \`${column.column_name
            }\``;
          sql += ` ${column.column_type}`;

          if (column.is_nullable == "YES") {
            sql += " NULL";
          } else {
            sql += " NOT NULL";
          }

          if (column.column_default != null) {
            sql += ` DEFAULT ${GetColumnDefaultValue(column.column_default)}`;
          }

          sql += ` COMMENT '${column.column_comment}';`;
        }

        ddls.push(sql);
      });
    });

    return ddls;
  }

  async status () {
    let index = 1;
    let rs = await this.queryForList(scripts.ShowStatus, null);

    (rs.datas || []).forEach(item => {
      item.rowIndex = index++;
      var statuItem = mysqlStatus.find(
        s => s.name == item.Variable_name
      );

      if (statuItem) {
        item.text = statuItem.text;
        item.describe = statuItem.describe;
      } else {
        item.text = item.Variable_name;
      }
    });

    return rs;
  }

  async monitor () {
    let rs = await ({
      at: dayjs().format('YYYY-MM-DD HH:mm:ss'),
      success: true,
      datas: []
    });

    return rs;
  }

  /**
   * 获取当前执行列表
   * @returns 
   */
  async processes () {
    let rs = await this.queryForList(scripts.ShowProcessList, null);

    (rs.datas || []).forEach(item => {
      item.CanKill = !(
        item.Id == 1 ||
        item.State == "starting" ||
        item.State == "Receiving from client" ||
        item.Command == "Daemon" ||
        item.Time == 0
      );
    });

    return rs;
  }

  async killProcess (processId) {
    if (!processId) {
      return {
        at: dayjs().format('YYYY-MM-DD HH:mm:ss'),
        success: false,
        message: '未指定任务编号！'
      };
    }

    let query = "";
    let ids = (processId + "").split(",");

    ids.forEach(v => (query += `kill ${v};`));

    let rs = await this.execute(query, null);

    rs.data = '终止成功！';

    return rs;
  }
}

const pools = new Map();
const defaultKeys = ["CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "NOW", "SYSDATE"];

/**
 * 获取数据库连接池。
 * @param {Number} dbIndex 数据库服务器标识
 * @returns {Pool} 数据库连接池
 */
function GetDbPool (dbIndex) {
  if (pools.get(dbIndex)) {
    return pools.get(dbIndex);
  }

  let config = getDbConfig(dbIndex);

  let pool = mysql.createPool({
    host: config.ip,
    user: config.account,
    password: config.password,
    database: config.database,
    port: config.port,
    insecureAuth: true,
    multipleStatements: true,
    timezone: '08:00'
  });

  pools.set(dbIndex, pool);

  return pool;
}

/**
 * 执行SQL查询。
 * @param {Connection} connection MySQL数据库连接对象
 * @param {String} sql SQL语句
 * @param {Array} values 参数数组
 * @returns {Promise} 异步操作对象
 */
let executeSQL = function (connection, sql, values) {
  return new Promise((resolve, reject) => {
    if (connection) {
      if (connection.state == "connected" || connection.state == "authenticated") {
        // 自定义参数化处理。
        connection.config.queryFormat = function (query, obj) {
          if (!obj) {
            return query;
          }

          let replacedProps = [];
          let properties = Object.getOwnPropertyNames(obj);

          // 非格式化处理(以#标记的参数)
          query = query.replace(/\#(\w+)/ig, function (txt, key) {
            let propName = properties.find(p => p.toLowerCase() == key.toLowerCase());

            if (propName) {
              let rs = obj[propName];

              replacedProps.push(propName);

              return rs || '';
            }

            return txt || '';
          }.bind(this));

          replacedProps.forEach(p => {
            delete obj[p];
          });

          // 格式化参数处理(以@标记的参数)
          query = query.replace(/\@(\w+)/ig, function (txt, key) {
            let propName = properties.find(p => p.toLowerCase() == key.toLowerCase());

            if (propName) {
              return this.escape(obj[propName]);
            }

            return txt;
          }.bind(this));

          return query;
        };

        connection.query(sql, values, (err, results, fields) => {
          if (err) {
            reject(err);
          } else {
            resolve(results);
          }
        });
      } else {
        reject({
          sqlMessage: `数据库连接失败(当前状态：${connection.state})！`
        });
      }
    } else {
      reject({
        sqlMessage: "数据库连接不能为null！"
      });
    }
  });
};

/**
 * 数据库处理封装。
 * @param {String} sql SQL语句
 * @param {any} values 参数
 * @param {Function | Number} callback 动态参数设置回调函数或数据库服务器标识(number型)
 * @param {Number} dbIndex 数据库服务器标识
 * @returns {Promise} 异步操作对象
 */
let query = function (sql, values, callback, dbIndex = 0) {
  let tempSql = sql;
  let builder = new SqlBuilder(values);

  if (typeof callback != 'function') {
    dbIndex = callback || 0;
  } else if (callback != null) {
    callback(builder);
  }

  tempSql += builder.build();

  let rs = {
    at: dayjs().format('YYYY-MM-DD HH:mm:ss'),
    success: false
  };

  return new Promise((resolve, reject) => {
    GetDbPool(dbIndex).getConnection((error, connection) => {
      if (error) {
        reject(error);
      } else {
        executeSQL(connection, tempSql, builder.getValues()).then(
          rows => {
            connection.release();

            rs.message = rows;
            rs.success = true;

            resolve(rs);
          },
          reason => {
            connection.release();
            rs.message = reason.sqlMessage || reason.message;

            reject(rs);
          }
        );
      }
    });
  });
};

/**
 * 数据库处理封装。
 * @param {String} sql SQL语句
 * @param {any} values 参数
 * @param {Function | Number} callback 动态参数设置回调函数或数据库服务器标识(number型)
 * @param {Number} dbIndex 数据库服务器标识
 * @returns {Promise} 异步操作对象
 */
let executeNonQuery = function (sql, values, callback, dbIndex = 0) {
  let tempSql = sql;
  let builder = new SqlBuilder(values);

  if (typeof callback != 'function') {
    dbIndex = callback || 0;
  } else if (callback != null) {
    callback(builder);
  }

  tempSql += builder.build();

  let rs = {
    at: dayjs().format('YYYY-MM-DD HH:mm:ss'),
    success: false
  };

  return new Promise((resolve, reject) => {
    GetDbPool(dbIndex).getConnection((error, connection) => {
      if (error) {
        reject(error);
      } else {
        executeSQL(connection, tempSql, builder.getValues()).then(
          rows => {
            connection.release();
            rs.success = true;

            resolve(rs);
          },
          reason => {
            connection.release();
            rs.message = reason.sqlMessage || reason.message;

            reject(rs);
          }
        );
      }
    });
  });
};

/**
 * 获取列的默认值。
 * @param value 默认值
 * @returns 默认值的显示
 */
function GetColumnDefaultValue (value) {
  if (defaultKeys.find(c => c.toUpperCase() == value.toUpperCase())) {
    return value;
  }

  return `'${value}'`;
}

module.exports = MySQLHelper;